﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace 控制台应用
{
    public class DBHelper
    {
        #region 属性
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string sqlConnString { get; set; } = "Persist Security Info = False; User ID = sa; Password = Sa123; Initial Catalog = SPPSdb; Data Source =127.0.0.1";

        /// <summary>
        /// sql语句
        /// </summary>
        public string sqlCommand { get; set; } = "select GETDATE();";

        private SqlConnection SqlConnection;
        #endregion

        #region 构造方法
        public DBHelper()
        {
            
        }
        public DBHelper(string strConn)
        {
            this.sqlConnString = strConn;
        }
        public DBHelper(string strConn, string strComm)
        {
            this.sqlConnString = strConn;
            this.sqlCommand = strComm;
        }
        #endregion

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        /// <returns></returns>
        public SqlConnection GetSqlConnection()
        {
            SqlConnection connection = new SqlConnection(sqlConnString);
            try
            {
                connection.Open();
                this.SqlConnection = connection;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return connection;
        }

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        /// <param name="strSqlConnection">数据库连接字符串</param>
        /// <returns></returns>
        public SqlConnection GetSqlConnection(string strSqlConnection) 
        {
            SqlConnection connection = new SqlConnection(strSqlConnection); 
            try
            {
                connection.Open();
                this.SqlConnection = connection;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return connection;
        }

        /// <summary>
        /// 执行Sql语句，返回DT
        /// </summary>
        /// <returns></returns>
        public DataTable ExcuteSqlWithSelectToDT()
        {
            SqlConnection connection = GetSqlConnection();
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sqlCommand, connection);
                SqlDataAdapter sda = new SqlDataAdapter(command);
                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }


        }


        /// <summary>
        /// 执行Sql语句，返回DT
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable ExcuteSqlWithSelectToDT(string strSql)
        {
            SqlConnection connection = GetSqlConnection();
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand(strSql, connection);
                SqlDataAdapter sda = new SqlDataAdapter(command);   
                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            

        }

        /// <summary>
        /// 执行Sql语句，返回DT
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        /// <param name="connection">Sql连接对象</param>
        /// <returns></returns>
        public DataTable ExcuteSqlWithSelectToDT(string strSql,SqlConnection connection)
        {
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand(strSql, connection);
                SqlDataAdapter sda = new SqlDataAdapter(command);
                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 执行Sql语句，返回DT
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        /// <param name="SqlConnectionString">Sql连接字符串</param>
        /// <returns></returns>
        public DataTable ExcuteSqlWithSelectToDT(string strSql, string SqlConnectionString)
        {
            SqlConnection connection = GetSqlConnection(SqlConnectionString);
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand(strSql, connection);
                SqlDataAdapter sda = new SqlDataAdapter(command);
                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
